<?php
namespace app\admin\service;
use think\Db;
use think\Loader;
class  Table{

    //订单导出表格
    function orderTableApi(){
        $uid = input('uid');
        $where = null;
        if(!empty($uid)){
            $where['a.u_uid'] = $uid;
        }
        $o_type_id = input('o_type_id');
        if(!empty($o_type_id)){
            $where['a.o_type_id'] = $o_type_id;
        }
        $o_state = input('o_state');
        if(!empty($o_state)){
            $where['a.o_state'] = $o_state;
        }
        $stime  = input('stime');
        $otime  = input('otime');
        if(!empty($stime) || !empty($otime)){
            if(!empty($stime) && !empty($otime)){
                $where['a.o_addtime']=array(array('egt',"{$stime}%"),array('elt',"{$otime}%")) ;
            }else if(!empty($stime)){
                $where['a.o_addtime'] = array('gt',$stime);
            }else{
                $where['a.o_addtime'] = array('gt',$otime);
            }
        }
        $join = [
            ['lt_user b','a.u_uid=b.u_uid'],
        ];
        $field    = 'a.o_no,a.order_id,a.o_type_id,a.o_money,a.o_state,a.o_addtime,a.o_paytime,b.u_phone,b.u_nickname';
        $data     = Db::name('order')->alias('a')->join($join)->where($where)->field($field)->order('a.o_addtime desc')->select();
        $date[][1]=array("订单号","用户昵称","产品名称","订单金额","订单状态","下单时间","支付时间");
        foreach ($data as $k=>$v) {
            if($v['o_state'] == 1){
                $type_name = '待付款';
            }else if($v['o_state'] == 2){
                $type_name = '已支付';
            }else if($v['o_state'] == 3){
                $type_name = '已服务';
            }else if($v['o_state'] == 4){
                $type_name = '已取消';
            }else{
                $type_name = '已转账申请审核';
            }
            $o_type_name = product_name($v['o_type_id']);
            $date[][$k]=array(
                ' '.$v['o_no'],
                $v['u_nickname']?$v['u_nickname']:$v['u_phone'],
                $o_type_name,
                $v['o_money'],
                $type_name,
                $v['o_addtime'],
                $v['o_paytime']!= '0000-00-00 00:00:00'?$v['o_paytime']:''
            );
        }

        $p='H';
        $firstly="订单号";
        $wdth=array(
            array('name'=>'B','value'=>30),
            array('name'=>'C','value'=>30),
            array('name'=>'D','value'=>30),
            array('name'=>'E','value'=>20),
            array('name'=>'F','value'=>30),
            array('name'=>'G','value'=>30),
            array('name'=>'H','value'=>30),
        );
        $filename="订单列表";
        $this->getExcel($filename,$date,$p,$wdth,$firstly);
    }


    //用户数据导出
    function userTableApi(){
        $phone = input('phone');
        $where = null;
        if(!empty($phone)){
            $where['u_phone'] = $phone;
        }
        $nickname = input('nickname');
        if(!empty($nickname)){
            $where['u_nickname'] = array('like',"$nickname%");
        }
        $user     = Db::name('user');
        $data     = $user->where($where)->field('u_phone,u_nickname,u_mail,u_addtime')->order('u_addtime desc')->select();
        $date[][1]=array("手机号","用户昵称","用户邮箱","注册时间");
        foreach ($data as $k=>$v) {
            $date[][$k]=array(
                ' '.$v['u_phone'],
                $v['u_nickname'].' ',
                $v['u_mail'].' ',
                $v['u_addtime']
            );
        }
        $p='E';
        $firstly="手机号";
        $wdth=array(
            array('name'=>'B','value'=>30),
            array('name'=>'C','value'=>30),
            array('name'=>'D','value'=>30),
            array('name'=>'E','value'=>20),
        );
        $filename="用户列表";
        $this->getExcel($filename,$date,$p,$wdth,$firstly);
    }

    //咨询列表导出
    function consulTableApi(){
        $where    = '';
        $uid      = input('uid');
        if(!empty($uid)){
            $where['b.u_phone'] = $uid;
        }
        $consult_type_id = input('consult_type_id');
        if(!empty($consult_type_id)){
            $where['a.consult_type_id'] = $consult_type_id;
        }
        $ct_state = input('ct_state');
        if(!empty($ct_state)){
            $where['a.ct_state'] = $ct_state;
        }else{
            $where['a.ct_state'] = 1;
        }
        $stime  = input('stime');
        $otime  = input('otime');
        if(!empty($stime) || !empty($otime)){
            if(!empty($stime) && !empty($otime)){
                $where['a.ct_addtime']=array(array('egt',"{$stime}%"),array('elt',"{$otime}%")) ;
            }else if(!empty($stime)){
                $where['a.ct_addtime'] = array('gt',$stime);
            }else{
                $where['a.ct_addtime'] = array('gt',$otime);
            }
        }
        $field    = "a.*,b.ct_name,c.u_phone,c.u_nickname";
        $join     = [
            ['lt_consult_type b','a.consult_type_id=b.consult_type_id','LEFT'],
            ['lt_user c','a.u_uid=c.u_uid','LEFT'],
        ];
        $data = Db::name('consult')->alias('a')->join($join)->where($where)->field($field)->order('a.ct_addtime desc')->select();
        $date[][1]=array("咨询用户","咨询标题","咨询类型","咨询状态","咨询问题","收藏/回复","咨询时间");
        foreach ($data as $k=>$v) {
            if($v['ct_state'] == 1){
                $ct_state_name = '可显示';
            }else{
                $ct_state_name = '已禁止';
            }
            $date[][$k]=array(
               $v['u_nickname']?' '.$v['u_nickname']:' '.$v['u_phone'],
                $v['ct_title'],
                $v['ct_name'],
                $ct_state_name,
                $v['ct_details'],
                $v['ct_sum'].'/'.$v['ct_count'],
                $v['ct_addtime']
            );
        }

        $p='H';
        $firstly="咨询用户";
        $wdth=array(
            array('name'=>'B','value'=>30),
            array('name'=>'C','value'=>50),
            array('name'=>'D','value'=>30),
            array('name'=>'E','value'=>20),
            array('name'=>'F','value'=>60),
            array('name'=>'G','value'=>10),
            array('name'=>'H','value'=>30),
        );
        $filename="咨询列表";
        $this->getExcel($filename,$date,$p,$wdth,$firstly);
    }

    //调用phpExcel
    function getExcel($fileName,$data,$p,$wdth,$firstly){
        Loader::import('PhpExcel.PHPExcel', EXTEND_PATH, '.class.php');
        //对数据进行检验
        if(empty($data)||!is_array($data)){
            die("data must be a array");
        }
        $date=date("Y_m_d",time());
        $fileName.="_{$date}.xls";
        //创建PHPExcel对象，注意，不能少了\
        $objPHPExcel=new \PHPExcel();
        $objProps=$objPHPExcel->getProperties();
        $column=1;
        $objActSheet=$objPHPExcel->getActiveSheet();
        $objPHPExcel->getActiveSheet()->getStyle()->getFont()->setName('微软雅黑');//设置字体
        $objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(30);//设置默认高度
        $objPHPExcel->getActiveSheet()->getColumnDimension()->setWidth('22');//设置默认高度

        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth('5');//设置列宽
        foreach ($wdth as $k=>$v){
            $objPHPExcel->getActiveSheet()->getColumnDimension($v['name'])->setWidth($v['value']);//设置列宽
        }

        //设置边框
        //设置边框
        $sharedStyle1=new \PHPExcel_Style();
        $sharedStyle1->applyFromArray(array('borders'=>array('allborders'=>array('style'=>\PHPExcel_Style_Border::BORDER_THIN))));

        foreach ($data as $ke=>$row){
            foreach($row as $key=>$rows){
                if(count($row)==1&&empty($row[0][1])&&empty($rows[1])&&!empty($rows)){
                    $objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "A{$column}:$p{$column}");//设置边框
                    array_unshift($rows,$rows['0']);
                    $objPHPExcel->getActiveSheet()->mergeCells("A{$column}:$p{$column}");//合并单元格
                    $objPHPExcel->getActiveSheet()->getStyle("A{$column}:$p{$column}")->getFont()->setSize(12);//字体
                    $objPHPExcel->getActiveSheet()->getStyle("A{$column}:$p{$column}")->getFont()->setBold(true);//粗体
                    //背景色填充
                    $objPHPExcel->getActiveSheet()->getStyle("A{$column}:$p{$column}")->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
                    $objPHPExcel->getActiveSheet()->getStyle("A{$column}:$p{$column}")->getFill()->getStartColor()->setARGB('FFB8CCE4');
                }else{
                    if(!empty($rows)){
                        array_unshift($rows,$key+1);
                        $objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1,"A{$column}:$p{$column}");//设置边框
                    }
                }
                if($rows['1']==$firstly){
                    //背景色填充
                    $objPHPExcel->getActiveSheet()->getStyle("A{$column}:$p{$column}")->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
                    $objPHPExcel->getActiveSheet()->getStyle("A{$column}:$p{$column}")->getFill()->getStartColor()->setARGB('FF4F81BD');
                }
                $objPHPExcel->getActiveSheet()->getStyle("A{$column}:$p{$column}")->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);//垂直居中
                $objPHPExcel->getActiveSheet()->getStyle("A{$column}:$p{$column}")->getAlignment()->setWrapText(true);//换行
                //行写入
                $span = ord("A");

                foreach($rows as $keyName=>$value){
                    // 列写入
                    $j=chr($span);
                    $objActSheet->setCellValue($j.$column, $value);
                    $span++;
                }
                $column++;

            }
        }
        //设置活动单指数到第一个表,所以Excel打开这是第一个表
        $objPHPExcel->setActiveSheetIndex(0);
        ob_end_clean();//清除缓冲区,避免乱码
        header('Content-Type: application/vnd.ms-excel');
        header("Content-Disposition:attachment;filename=\"$fileName\"");
        header('Cache-Control: max-age=0');
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output'); //文件通过浏览器下载
        exit;
    }
}
